#!/usr/bin/env python
#coding=utf-8
from sqlalchemy import ForeignKey
from sqlalchemy.orm import relation, backref
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Table, Column, Integer, String, MetaData, ForeignKey,create_engine
from sqlalchemy.orm import sessionmaker
from sqlalchemy.orm import join
from sqlalchemy.sql import func
from sqlalchemy.orm import mapper, relation
from sqlalchemy.ext.associationproxy import association_proxy

#engine = create_engine('sqlite:///SimpleSite/development.db', echo=True)
engine = create_engine('sqlite://')
metadata = MetaData()

#定义表的结构
users_table = Table('users', metadata,
    Column('id', Integer, primary_key=True),
    Column('name', String(64)),
)

keywords_table = Table('keywords', metadata,
    Column('id', Integer, primary_key=True),
    Column('keyword', String(64))
)

userkeywords_table = Table('userkeywords', metadata,
    Column('user_id', Integer, ForeignKey("users.id"),
           primary_key=True),
    Column('keyword_id', Integer, ForeignKey("keywords.id"),
           primary_key=True)
)
#创建表
metadata.create_all(engine) 

class User(object):
    def __init__(self, name):
        self.name = name
    keywords = association_proxy('kw', 'keyword')

class Keyword(object):
    def __init__(self, keyword):
        self.keyword = keyword

mapper(User, users_table, properties={
    'kw': relation(Keyword, secondary=userkeywords_table)
    })
mapper(Keyword, keywords_table)


# iterate through child objects via association, including association
# attributes
Session = sessionmaker(bind=engine)
session=Session()

user = User('jek')
user.kw.append(Keyword('cheese inspector'))
print user.kw
# [<__main__.Keyword object at 0xb791ea0c>]
print user.kw[0].keyword
# 'cheese inspector'
print [keyword.keyword for keyword in user.kw]
# ['cheese inspector']

#session.add(user)
#session.flush()
user.keywords.append('snack ninja')
print user.keywords



#session.commit()